SQL Editor - Build Subquery
The Add Query button allows you to create additional queries for your conditional WHERE clause. A subquery is frequently used in the where clause to limit the number of records returned in the main query. You can create a subquery in the Visual Designer or Free Type.
Scalar Subquery
Some databases allow entire SQL statements to be used as a field in the SELECT clause of another query. As each row is processed in the main query, the subquery is called to fill in the value for that particular field. These subqueries must return a single value (or no value) for each row in the main query.
select last_name, first_name, ID, (select SSN from SSN_TABLE where ID=ID) as "SSN"
from person_table
This technique can be used for retrieving a data element from a table if you don’t wish to include that table in the main query. This can be a useful way to avoid the use of outer joins, which can negatively impact query performance.
- Create a calculated field.
- Click the ellipsis next to Field to define the calculated field. Instead of including simple fields and expressions, you will actually create an entire SQL statement, enclosed in a set of parenthesis. Remember that the scalar subquery must link to the main query and must return zero or one row.
- As for all calculated fields, make sure to give the field an alias.
Correlated Subquery
Some queries require that the output be limited by the results of a subquery. The subquery is linked to the main query (in the very last line). As each row from the main query is examined, it will be compared against the results of the subquery. These types of subqueries are called “correlated” as the results of the main query are dependent on the results of the subquery. In most cases the main query and the subquery use the same table. Whenever a table is used multiple times in the same query, it is important to add a table alias for each instance of the table (Argos adds table aliases automatically).
select last_name, first_name, ID
from people
where status="A"
and select max(effective_date) "MaxDate"
from people as people1
where people_id=people1_id
Building a correlated subquery using the Visual SQL Builder is not difficult, but does require several steps:
Step #1: Build the main query
Step #2: Build the correlated subquery
From the main query design, click the Add Query button to begin building your subquery. You do not need to add the starting and ending parenthesis, Argos will do that automatically for any subquery.
Step #3: Add the subquery to the WHERE clause of the main query
Now that you’ve built your main query and your subquery, you only need to place the subquery into the appropriate section of the main query. To perform this linkage, click the Link with Query button on your subquery. This opens the WHERE clause in your main query and asks you what operator you would like to use.
Subquery Options
Button | Description |
---|---|
Add the subquery to the main query's WHERE clause. | |
Edit the subquery. | |
Delete the subquery. |
Non-Correlated Subquery
Sometimes it is useful to create a subquery that is not directly related to the main query. It is possible to accomplish this with some tricky join logic, but a “non-correlated” subquery is often easier. These types of subqueries are generally not tied to a particular field in the main query using the “=” operator, rather they typically use other operators such as <. >, IN, NOT IN, EXISTS and NOT EXISTS. These are called non-correlated subqueries as the subquery is not really dependent on the main query data, the results of the subquery are the same regardless of which row is being examined in the main query. This can also result in faster performance for many queries.
Sample Non-Correlated Subquery
select employees.last_name, employees.first_name
from hr.employees employees
where employees.department_id in
(select departments.department_id
from hr.departments departments
where departments.department_name = "Finance")
Inline View Subquery
An inline view subquery allows you to create a query, the results of which can be used as a table in a FROM clause. Once the inline view subquery is created, it is added to Visible Fields (SELECT) tab for the main query as if it were a table. See Inline View Subquery in the DataBlock Designer Guide for more information.
Some details to keep in mind when using inline views:
- If you are using an Oracle database, the alias of the inline view, when specified in the main query, must be enclosed within quotation marks. When you select the name of the field on the Visible Fields (SELECT) tab, the dropdown displays the alias name both with and without quotes.
- If you are using an Oracle database (and have field alias names within quotes) and you want to use the alias name in a report, you must use the alias name without quotation marks in the form design.
If you need additional help creating DataBlocks, refer to the Getting Started with DataBlocks page.
To read step-by-step tutorials on how to create subqueries, refer to Example 10 (Scalar Subquery), Example 11( Correlated Subquery), or Example 12 (Non-Correlated Subquery), in the DataBlock Designer Guide.